package sparksql_fenxi.T2

import org.apache.spark.sql.SparkSession

object t1 {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder()
      .appName("test")
      .master("local[*]")
      .enableHiveSupport()
      .getOrCreate()


    spark.table("test.score")
      .createTempView("score")

    spark.table("test.course")
      .createTempView("course")

    spark.table("test.teacher")
      .createTempView("teacher")

    spark.table("test.student")
      .createTempView("student")

    // todo 第一题:查询所有学生的学号，姓名，选课总数，和所有课程的总成绩
    val r1 = spark.sql(
      """
        |select distinct
        |s_id,
        |s_name,
        |count(*) over(partition by s_id,s_name) as course_count,
        |sum(s_score) over(partition by s_id,s_name) as sum_score
        |from(
        |select
        |a.s_id,
        |b.s_name,
        |a.c_id,
        |a.s_score
        |from score as a
        |join student as b
        |on b.s_id=a.s_id
        |) as r1
        |""".stripMargin)

    r1.show


    spark.close()
  }

}
